# -*- coding:utf-8 -*-
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
cxn=MySQLdb.connect(db='pachong',host='localhost',user='root',passwd='123456ms')
cxn.set_character_set('utf8')
cur=cxn.cursor()
decode_list=['utf-8', 'gb2312', 'gbk', 'gb18030', 'cp1252', ]
def decode_data(data):
    for coding in decode_list:
        try:
            data=data.decode(coding)
            return data,coding
        except:
            pass
    return u'',''
S_n51cto_allow_domains=[#'http://forum.csdn.net/',
                       ]
S_n51cto_deny_domains=['http://down.51cto.com',
                        'http://job.51cto.com',
                        'http://home.51cto.com',
                        '/tagindex.php',
                        '/search.php',
                        'http://selftest.51cto.com',
                        'http://down.51cto.com/',
                        '/member.php',
                        'http://bbs.51cto.com/space',
                        '/stats.php',
                        '/post.php',
                        '/faq.php',
                        '/search.php',
                        '/misc.php',
                        '/attachment.php',
                        '/subscribe.php',
                        '/my.php',
                        'http://t.51cto.com',
                        'http://tool.51cto.com',
                        '/memcp.php',
                        '/blog.php',
                        '/digest.php',
                        '/viewthread.php',
                        '/feedbackt.php',
                        '/redirect.php',
                        'http://www.51cto.com/php/',
                        '/authart.php',
                        '/userface.php',
                        '/rss.php',
                        'http://blog.51cto.com/js/',
                        '/addgroup.php',
                        '/auth4.php',
                        '/auth_new_spec.php',
                        '/medal.service_new.php',
                        '/tag.php',
                        '/post_mz.php',
                        '/viewthread_mz.php',
                        '/group_index.php',
                        '/article.php',
                        'http://new.51cto.com/about/guestbook/index.php',
                        '51cto.com/pic/',
                        '/addblog.php',
                        '51cto.com/rss',
                        'blog.51cto.com/mygroup.php',
                        'blog.51cto.com/pic',
                        'xmlrpc.php',
                        'article.php',
                        'user_pic_com.php',
                        '/space-uid-',
                        'http://bbs.51cto.com:80',
                        'http://zhidao.51cto.com/person',
                        'http://bbs.51cto.com/wap/',
                      ]
deny_url_list=S_n51cto_deny_domains
allow_url_list=S_n51cto_allow_domains
table_name='n51cto'
def is_deny_url(url):
    url=url.lower().strip()
    for aurl in allow_url_list:
        if aurl.lower() in url:
            return False
    for durl in deny_url_list:
        if durl.lower() in url:
            return True
    return False
    

def del_url(table_name):
    print table_name
    sql='select min(id),max(id) from %s'%table_name
    cur.execute(sql)
    min_id,max_id=cur.fetchone()            
    print min_id,max_id
    record_num=0
    step=10**6
    idx=min_id
    while idx<=max_id:
        sql_pref='select id,url from %s'%table_name
        sql=sql_pref+' where id between %s and %s'
        cur.execute(sql,(str(idx),str(idx+step)))
        for record in cur.fetchall():
            url=record[1]
            url=decode_data(url)[0]
            if is_deny_url(url):
                #print record[1]
                sql_prefix='delete from %s'%table_name
                sql=sql_prefix+' where id=%s'
                cur.execute(sql,str(record[0]))
                record_num+=1
                if not record_num%10**4:
                    print record_num
        cxn.commit()
                
        idx+=step
    print record_num
table_list=[table_name,'%s_queue'%table_name]
for table in table_list:
    del_url(table)
    
cxn.commit()
cur.close()
cxn.close()


print 'end'
